Oracle | 您所在的位置:网站首页 › access cdate › Oracle |
oracle 查询当天记录 三种方法效率比较
-- 查询一表中当天生成的数据 -- 原表mobilefrends中的cdate字段上有索引,创建索引语句是:create index mobilefrends_cdate_idx on mobilefrends(cdate);
--------------------------------------------------------------------------------------------------------------------- -- 方法一:用to_char()函数
hll@SZTYORA> select count(*) from mobilefrends where to_char(cdate,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd');
COUNT(*) ---------- 82119
已用时间: 00: 00: 17.18
执行计划 ---------------------------------------------------------- Plan hash value: 3731074549
------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 8 | 11773 (16)| 00:02:22 | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | INDEX FAST FULL SCAN| MOBILEFRENDS_CDATE_IDX | 129K| 1012K| 11773 (16)| 00:02:22 | ------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - filter(TO_CHAR(INTERNAL_FUNCTION("CDATE"),'yyyy-mm-dd')=TO_CHAR(SYSDATE@!-1,'yyyy -mm-dd'))
统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 36896 consistent gets 0 physical reads 0 redo size 345 bytes sent via SQL*Net to client 338 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
--------------------------------------------------------------------------------------------------------------------- -- 方法二:用trunc()函数
hll@SZTYORA> select count(*) from mobilefrends where trunc(cdate)=trunc(sysdate);
COUNT(*) ---------- 82119
已用时间: 00: 00: 16.32
执行计划 ---------------------------------------------------------- Plan hash value: 3731074549
------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 8 | 12580 (22)| 00:02:31 | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | INDEX FAST FULL SCAN| MOBILEFRENDS_CDATE_IDX | 129K| 1012K| 12580 (22)| 00:02:31 | ------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - filter(TRUNC(INTERNAL_FUNCTION("CDATE"))=TRUNC(SYSDATE@!-1))
统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 36896 consistent gets 0 physical reads 0 redo size 345 bytes sent via SQL*Net to client 338 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
--------------------------------------------------------------------------------------------------------------------- -- 方法三:不用函数 hll@SZTYORA> select count(*) from mobilefrends where cdate>=trunc(sysdate) and cdate |
CopyRight 2018-2019 实验室设备网 版权所有 |